package com.summer.filesync.database.dao.impl;

import com.summer.filesync.database.DatabaseManager;
import com.summer.filesync.database.dao.FileMetadataDAO;
import com.summer.filesync.model.FileMetadata;
import com.summer.filesync.util.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/** 文件元数据DAO实现类 使用SQLite数据库存储文件元数据 */
public class FileMetadataDAOImpl implements FileMetadataDAO {

  private static final Logger logger = Logger.getLogger(FileMetadataDAOImpl.class);
  private final DatabaseManager databaseManager;

  public FileMetadataDAOImpl() {
    this.databaseManager = DatabaseManager.getInstance();
  }

  @Override
  public FileMetadata save(FileMetadata entity) {
    String sql =
        """
        INSERT INTO file_metadata
        (file_path, file_size, last_modified, checksum, is_directory, node_id, created_time, updated_time)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """;

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

      long now = System.currentTimeMillis();

      stmt.setString(1, entity.getFilePath());
      stmt.setLong(2, entity.getFileSize());
      stmt.setLong(3, entity.getLastModified());
      stmt.setString(4, entity.getChecksum() != null ? entity.getChecksum() : entity.getFileHash());
      stmt.setBoolean(5, entity.isDirectory());
      stmt.setString(6, entity.getNodeId());
      stmt.setLong(7, now);
      stmt.setLong(8, now);

      int affectedRows = stmt.executeUpdate();
      if (affectedRows == 0) {
        throw new SQLException("Creating file metadata failed, no rows affected.");
      }

      try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
        if (generatedKeys.next()) {
          entity.setId(generatedKeys.getLong(1));
          entity.setCreatedAt(now);
          entity.setUpdatedAt(now);
          return entity;
        } else {
          throw new SQLException("Creating file metadata failed, no ID obtained.");
        }
      }

    } catch (SQLException e) {
      logger.error("Error saving file metadata: " + entity.getFilePath(), e);
      throw new RuntimeException("Failed to save file metadata", e);
    }
  }

  @Override
  public Optional<FileMetadata> findById(Long id) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata WHERE id = ?
        """;

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setLong(1, id);

      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          return Optional.of(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by ID: " + id, e);
    }

    return Optional.empty();
  }

  @Override
  public Optional<FileMetadata> findByFilePath(String filePath) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata WHERE file_path = ?
        """;

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setString(1, filePath);

      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          return Optional.of(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by path: " + filePath, e);
    }

    return Optional.empty();
  }

  @Override
  public List<FileMetadata> findByFileHash(String fileHash) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata WHERE checksum = ?
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setString(1, fileHash);

      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          results.add(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by hash: " + fileHash, e);
    }

    return results;
  }

  @Override
  public List<FileMetadata> findAll() {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata ORDER BY file_path
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery()) {

      while (rs.next()) {
        results.add(mapResultSetToFileMetadata(rs));
      }

    } catch (SQLException e) {
      logger.error("Error finding all file metadata", e);
    }

    return results;
  }

  @Override
  public FileMetadata update(FileMetadata entity) {
    String sql =
        """
        UPDATE file_metadata SET
            file_size = ?, last_modified = ?, checksum = ?,
            is_directory = ?, node_id = ?, updated_time = ?
        WHERE id = ?
        """;

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      long now = System.currentTimeMillis();

      stmt.setLong(1, entity.getFileSize());
      stmt.setLong(2, entity.getLastModified());
      stmt.setString(3, entity.getChecksum() != null ? entity.getChecksum() : entity.getFileHash());
      stmt.setBoolean(4, entity.isDirectory());
      stmt.setString(5, entity.getNodeId());
      stmt.setLong(6, now);
      stmt.setLong(7, entity.getId());

      int affectedRows = stmt.executeUpdate();
      if (affectedRows == 0) {
        throw new SQLException("Updating file metadata failed, no rows affected.");
      }

      entity.setUpdatedAt(now);
      return entity;

    } catch (SQLException e) {
      logger.error("Error updating file metadata: " + entity.getFilePath(), e);
      throw new RuntimeException("Failed to update file metadata", e);
    }
  }

  @Override
  public boolean deleteById(Long id) {
    String sql = "DELETE FROM file_metadata WHERE id = ?";

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setLong(1, id);

      int affectedRows = stmt.executeUpdate();
      return affectedRows > 0;

    } catch (SQLException e) {
      logger.error("Error deleting file metadata by ID: " + id, e);
      return false;
    }
  }

  @Override
  public boolean delete(FileMetadata entity) {
    return deleteById(entity.getId());
  }

  @Override
  public long count() {
    String sql = "SELECT COUNT(*) FROM file_metadata";

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery()) {

      if (rs.next()) {
        return rs.getLong(1);
      }

    } catch (SQLException e) {
      logger.error("Error counting file metadata", e);
    }

    return 0;
  }

  @Override
  public boolean existsById(Long id) {
    String sql = "SELECT 1 FROM file_metadata WHERE id = ? LIMIT 1";

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setLong(1, id);

      try (ResultSet rs = stmt.executeQuery()) {
        return rs.next();
      }

    } catch (SQLException e) {
      logger.error("Error checking existence by ID: " + id, e);
      return false;
    }
  }

  @Override
  public List<FileMetadata> findByLastModifiedBetween(long startTime, long endTime) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata
        WHERE last_modified BETWEEN ? AND ?
        ORDER BY last_modified DESC
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setLong(1, startTime);
      stmt.setLong(2, endTime);

      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          results.add(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by last modified range", e);
    }

    return results;
  }

  @Override
  public List<FileMetadata> findByFileSizeBetween(long minSize, long maxSize) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata
        WHERE file_size BETWEEN ? AND ?
        ORDER BY file_size DESC
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setLong(1, minSize);
      stmt.setLong(2, maxSize);

      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          results.add(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by file size range", e);
    }

    return results;
  }

  @Override
  public List<FileMetadata> findByDirectory(String directoryPath) {
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata
        WHERE file_path LIKE ?
        ORDER BY file_path
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setString(1, directoryPath + "%");

      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          results.add(mapResultSetToFileMetadata(rs));
        }
      }

    } catch (SQLException e) {
      logger.error("Error finding file metadata by directory: " + directoryPath, e);
    }

    return results;
  }

  @Override
  public List<FileMetadata> findFilesNeedingSync() {
    // 这里可以根据具体的同步策略来定义
    // 例如：找到本地文件修改时间比数据库记录更新的文件
    String sql =
        """
        SELECT id, file_path, file_size, last_modified, checksum,
               is_directory, node_id, created_time, updated_time
        FROM file_metadata
        WHERE updated_time < last_modified
        ORDER BY last_modified DESC
        """;

    List<FileMetadata> results = new ArrayList<>();

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery()) {

      while (rs.next()) {
        results.add(mapResultSetToFileMetadata(rs));
      }

    } catch (SQLException e) {
      logger.error("Error finding files needing sync", e);
    }

    return results;
  }

  @Override
  public int batchSave(List<FileMetadata> fileMetadataList) {
    String sql =
        """
        INSERT INTO file_metadata
        (file_path, file_size, last_modified, checksum, is_directory, node_id, created_time, updated_time)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """;

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      conn.setAutoCommit(false);
      long now = System.currentTimeMillis();

      for (FileMetadata entity : fileMetadataList) {
        stmt.setString(1, entity.getFilePath());
        stmt.setLong(2, entity.getFileSize());
        stmt.setLong(3, entity.getLastModified());
        stmt.setString(
            4, entity.getChecksum() != null ? entity.getChecksum() : entity.getFileHash());
        stmt.setBoolean(5, entity.isDirectory());
        stmt.setString(6, entity.getNodeId());
        stmt.setLong(7, now);
        stmt.setLong(8, now);

        stmt.addBatch();
      }

      int[] results = stmt.executeBatch();
      conn.commit();

      return results.length;

    } catch (SQLException e) {
      logger.error("Error batch saving file metadata", e);
      throw new RuntimeException("Failed to batch save file metadata", e);
    }
  }

  @Override
  public int deleteByDirectory(String directoryPath) {
    String sql = "DELETE FROM file_metadata WHERE file_path LIKE ?";

    try (Connection conn = databaseManager.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

      stmt.setString(1, directoryPath + "%");

      return stmt.executeUpdate();

    } catch (SQLException e) {
      logger.error("Error deleting file metadata by directory: " + directoryPath, e);
      return 0;
    }
  }

  /** 将ResultSet映射为FileMetadata对象 */
  private FileMetadata mapResultSetToFileMetadata(ResultSet rs) throws SQLException {
    FileMetadata metadata = new FileMetadata();
    metadata.setId(rs.getLong("id"));
    metadata.setFilePath(rs.getString("file_path"));
    metadata.setFileSize(rs.getLong("file_size"));
    metadata.setLastModified(rs.getLong("last_modified"));
    String checksum = rs.getString("checksum");
    metadata.setChecksum(checksum);
    metadata.setFileHash(checksum); // 为了兼容性，设置两个字段
    metadata.setDirectory(rs.getBoolean("is_directory"));
    metadata.setNodeId(rs.getString("node_id"));
    metadata.setCreatedAt(rs.getLong("created_time"));
    metadata.setUpdatedAt(rs.getLong("updated_time"));
    return metadata;
  }
}
